package component
{
	import flash.data.SQLConnection;
	import flash.data.SQLStatement;
	import flash.filesystem.File;
	
	import mx.collections.ArrayCollection;
	

	public class DataBaseManager
	{
		protected var sqlConnection:SQLConnection;
		public function DataBaseManager()
		{
			sqlConnection = new SQLConnection();
			sqlConnection.open(File.applicationStorageDirectory.resolvePath("starworld.db"));
		}
		
		public function init():void {
			createMyList();
			createMyData();
			
		}
		
		private function createMyList():void {
			
			if (sqlConnection != null) {
				var stmt:SQLStatement = new SQLStatement();
				stmt.sqlConnection = sqlConnection;
				stmt.text = "CREATE TABLE IF NOT EXISTS MYLIST (" +
							"idx INTEGER PRIMARY KEY AUTOINCREMENT, " +
							"view INTEGER , " +
							"site STRING, " + 
							"cate STRING, " +
							"url STRING, " +
							"timeUpdate DATE)";

				stmt.execute();
			}
		}
		
		private function createMyData():void {
			
			if (sqlConnection != null) {
				var stmt:SQLStatement = new SQLStatement();
				stmt.sqlConnection = sqlConnection;
				stmt.text = "CREATE TABLE IF NOT EXISTS MYDATA (" +
					"idx INTEGER, " +
					"title STRING, " + 
					"url STRING, " +
					"discript TEXT)";
				
				stmt.execute();
			}
		}
		
		private function initMyList(result:*):void {
			
			if(result.result.item){		
				
				for each (var r:Object in result.result.item )  {
					
					r.site;
					r.cate;
					r.url;
					r.timeUpdate;
					
				}
			}
		}
		
		
		public function insertMyList(site:String, cate:String, url:String):void {
			
			//SELECT STRFTIME('%Y-%m-%d %H:%M:%S',my_column) as my_column_formatted FROM my_table
			if (sqlConnection != null) {
				var stmt:SQLStatement = new SQLStatement();
				stmt.sqlConnection = sqlConnection;
				stmt.text = "INSERT into MYLIST values(:view,:site,:cate,:url,:timeUpdate)";
				stmt.parameters[":view"] = 0;
				stmt.parameters[":site"] = site;
				stmt.parameters[":cate"] = cate;
				stmt.parameters[":url"] = url;
				stmt.parameters[":timeUpdate"] = new Date();
				stmt.execute();
			}
		}
		
		public function getViewIdx():int
		{
			var result:int = 0;
			var stmt:SQLStatement = new SQLStatement();
			stmt.sqlConnection = sqlConnection;
			stmt.text = "SELECT idx FROM MYLIST where view=1";
			stmt.execute();
			var arr:Array = stmt.getResult().data;
			
			if (arr.length > 0) {
				result = int(arr[0]);
			}
			
			return result;
		}
		
		public function getViewData():ArrayCollection
		{
			var result:int = 0;
			var stmt:SQLStatement = new SQLStatement();
			stmt.sqlConnection = sqlConnection;
			stmt.text = "SELECT title, url, discript FROM MYLIST WHERE view=1";
			stmt.execute();

			var arr:ArrayCollection =  new ArrayCollection(stmt.getResult().data);
			
			return arr;
		}
		
		
	}
}